Sequence Analytics API in Dune

Learn how to use the Sequence Builder analytics feature to query user data for your project with a serverless Cloudflare Worker.

Time to complete: 20-30 minutes

In this guide, we will show you how to use the Sequence Builder analytics feature to query information about usage from your users for your specific project leveraging a serverless Cloudflare Worker.

Showcase to your community how well you’re doing via a Dune dashboard to give a sense of connectiveness, or, use the produced API to incorporate intelligent feedback loops into your game driven by user analytics.

You can view an example of the output for this guide here

1. Access Key Management: Claim a secret access key to interact with the Sequence stack
2. Cloudflare Worker: Create a function that queries the Sequence stack and produces project specific data points
3. Dune Dashboard: Create a view into the data as a shareable dashboard

A reference to the template code can be found here

1. Access Key Management

You’ll need to acquire a Secret Access Key for your project in order to authenticate your application with the Sequence stack. Use the following steps:

Secret Access Key Creation

1. Access Settings

   First start by accessing settings, and selecting the API Keys card:

   
2. Add Service Account

   Scroll down and select + Add Service Account:

   
3. Select Write Permission

   Then change the permission to Write, click on + Add Service Account, and select Confirm:

   

   Finally copy the key and store it in a safe location, as you will not have access to this in the future from the Sequence Builder.

2. Cloudflare Worker

In this example, we utilize a Cloudflare Worker to experience automatic scaling based on dashboard use, and easy deployments from the cli, but of course you can use your own backend or other serverless alternatives.

1. Create Project

   In order to create the project from scratch, first create a project with mkdir, cd into the project, and run pnpm init to create a package.json.

2. 'Hello World' Worker

   Make sure wrangler cli is installed in your project and set the wrangler keyword as an alias in your local bash session.

   pnpm install wrangler --save-dev
   alias wrangler='./node_modules/.bin/wrangler'
   

   Create an account on the Cloudflare site and perform a login step to login to your Cloudflare dashboard to connect the Cloudflare platform to your local development environment.

   wrangler login
   

   Once logged in, initialize the project in the directory with the command wrangler init and accept one of the randomly generated project folder names provided that you like, and follow the prompts to initialize your git tracked typescript "Hello World" Worker application.

   wrangler init
   

   To complete this step, you should press enter 4 times after wrangler init with the last 2 step answered as No to decline git versioning and deployment.

   This will clone down a starter repository that can be used to deploy code to the cloud.

   Local API Testing
   At any point in the guide, you can use the wrangler dev command in the project folder for local testing

   Deploy Test

   Finally, cd into the randomly generated project folder, and perform a wrangler deploy command.

   This should print a URL, which you can enter in the browser the URL https://<app>.<account>.workers.dev to view the Hello World! result.

3. Setup Config, Routes, & Mock functions

   Once you have the project setup, update your wrangler.toml with the following variables, where DAYS is the time period you’re looking at:

   [vars]
   SECRET_API_ACCESS_KEY = "<SECRET_API_ACCESS_KEY>"
   PROJECT_ID = <PROJECT_ID>
   DAYS = <DAYS>
   

   Then include the Env type with the variables in index.ts:

   export interface Env {
     PROJECT_ID: number;
     SECRET_API_ACCESS_KEY: string;
     DAYS: number;
   }
   

   Replace the existing fetch function with the following mocked function calls:

   export default {
     async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
       const url = new URL(request.url);
   
       // Handle different endpoints
       if (url.pathname === "/dailyActiveUsers") {
         return handleDailyWallets(env, request);
       } else if (url.pathname === "/totalTransactionsSent") {
         return handleTotalTxns(env, request);
       } else {
         return new Response("No function for this URL", {status: 405});
       }
     }
   };
   

   Using the following functions:

   const handleDailyWallets = async (env: Env, request: Request) => {
     return new Response(JSON.stringify({endpoint: "daily"}), {status: 200});
   };
   
   const handleTotalTxns = async (env: Env, request: Request) => {
     return new Response(JSON.stringify({endpoint: "total"}), {status: 200});
   };
   

4. Date Formatting

   Next, include the following utility functions to parse the correct date from the updated value in the wrangler.toml for the DAYS variable:

   const endDate = () => {
     const today = new Date();
     return today.toISOString().substring(0, 10); // only including the YYYY-MM-DD date
   };
   
   const startDate = (env: Env) => {
     const today = new Date();
   
     // Format today's date as a string
     const daysBefore = new Date(today);
     daysBefore.setDate(daysBefore.getDate() - env.DAYS);
   
     // Format the date 7 days before as a string by only including the YYYY-MM-DD date
     const daysBeforeString = daysBefore.toISOString().substring(0, 10);
     return daysBeforeString;
   };
   

5. Daily Active Users

   Now, handle the Daily Active Users request by using the following function, that calls the Sequence Analytics API:

   const handleDailyWallets = async (env: Env, request: Request) => {
     const resp = await fetch(`https://api.sequence.build/rpc/Analytics/WalletsDaily`, {
       method: "POST",
       headers: {
         "Content-Type": "application/json",
         Authorization: `Bearer ${env.SECRET_API_ACCESS_KEY}`
       },
       body: JSON.stringify({
         filter: {
           dateInterval: "DAY",
           endDate: endDate(),
           projectId: env.PROJECT_ID,
           startDate: startDate(env)
         }
       })
     });
   
     const data: any = await resp.json();
     return new Response(JSON.stringify(data.walletStats), {status: 200});
   };
   

6. Total Transactions Sent

   Finally, add the following function for the Total Transactions Sent:

   const handleTotalTxns = async (env: Env, request: Request) => {
     const resp = await fetch(
       `https://api.sequence.build/rpc/Analytics/WalletsTxnSentTotal`,
       {
         method: "POST",
         headers: {
           "Content-Type": "application/json",
           Authorization: `Bearer ${env.SECRET_API_ACCESS_KEY}`
         },
         body: JSON.stringify({
           filter: {
             dateInterval: "DAY",
             endDate: endDate(),
             projectId: env.PROJECT_ID,
             startDate: startDate(env)
           }
         })
       }
     );
   
     const data: any = await resp.json();
     return new Response(JSON.stringify(data.walletStats), {status: 200});
   };
   

7. Included Spacing for Days of Zero Data

   Included in the Sequence Analytics API is the days with zero activity removed from the response. However, if you want to include days with zero data in your Dune queries to showcase the spacing of time in relation of data, you can use the following function to complete the days not showing data in the correct date format:

   const fillMissingDates = (data: any[], startDate: string, endDate: string) => {
     const filledData: {value: number; label: string}[] = [];
     const start = new Date(startDate);
     const end = new Date(endDate);
   
     for (let d = new Date(start); d <= end; d.setDate(d.getDate() + 1)) {
       const dateString = d.toISOString().substring(0, 10);
       const existingData = data.find((entry) => entry.label === dateString);
       if (existingData) {
         filledData.push(existingData);
       } else {
         filledData.push({value: 0, label: dateString});
       }
     }
   
     return filledData;
   };
   

   Then for both call responses include the following method call with the walletStats data passed in:

   	...
   	const data: any = await resp.json();
   	const filledData = fillMissingDates(data.walletStats, startDate(env), endDate());
   	return new Response(JSON.stringify(filledData), { status: 200 });
   }
   

Now you can test your API by calling the respective paths with host name (/dailyActiveUsers & /totalTransactionsSent) once you have redeployed with wrangler deploy.

For more examples of endpoints available through the Analytics API, please check out the overview page.

3. Dune Dashboard

1. Dune Sign Up

   First, sign up to Dune

2. Create Query

   Access your account at https://dune.com/<account> and select the Create button and New query.

   
3. Daily Active Users Query

   Input the following SQL query in the console, and select Run:

   SELECT
     t.label as "Date", -- converting the label to "Date"
     t.value as "Count" -- converting the value field to "Count"
   FROM UNNEST(
     TRY_CAST(
       JSON_PARSE(HTTP_GET('https://<URL>/dailyActiveUsers')) AS ARRAY(ROW(label VARCHAR, value DOUBLE))
     )
   ) AS t(label, value)
   
   

   Once the results have been returned, create a New visualization.

   Then, select Add visualization after the default Bar chart is selected from the dropdown (but feel free to customize this).

   

   Finally, click Save and add a name to your query.

   
4. Total Transactions Sent Query

   Repeat the steps from the previous step and use the following SQL query:

   SELECT
     t.label,
     t.value
   FROM UNNEST(
     TRY_CAST(
       JSON_PARSE(HTTP_GET('https://<URL>/totalTransactionsSent')) AS ARRAY(ROW(label VARCHAR, value DOUBLE))
     )
   ) AS t(label, value)
   
   

   Once the results have been returned, create a New visualization.

   Then, select Add visualization and scroll down to Counter to create a counter widget that returns the absolute total returned from the API.

5. Create New Dashboard

   Access the buttons that read Create > New dashboard and input a name for the new dashboard.

   

   Once created add the previous 2 queries by selecting Edit and Add visualization.

   

   

   Once for each query, search for the name in the modal and select Add for each query, then Done in the modal, and Done in the dashboard.

Congratulations, you’re now ready to share your project’s data usage with your teammates or community. Finish by clicking the Share button.